package com.revenco.bi.util;

import java.text.ParseException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.nutz.lang.Lang;
import org.springframework.util.StringUtils;

import com.revenco.bi.model.userbean.CFieldsBean;
import com.revenco.bi.model.userbean.YFieldsBean;


public class SqlTemplate {
	String aliaTabX = "";
	String aliaTabY = "";
	
	/**
	 * sql各个片段的拼接
	 * 
	 * @param xFieldMap
	 * @param yField
	 * @param fromTable
	 * @param joinTable
	 * @param relaField
	 * @param cfieldsArray
	 * @return
	 */
	public String getSqlDetail(Map<String, String> xFieldMap,
			YFieldsBean[] yField, String fromTable, String joinTable,
			String relaField, CFieldsBean[] cfieldsArray, 
			String mulRelationData, String relation, String mulRelaTable,
			Map<String, String> map,String xOriginalTable) {
		aliaTabY = map.get(fromTable);
		aliaTabX = map.get(joinTable);
		if ("".equals(joinTable) || joinTable == null) {
			aliaTabY = aliaTabX;
			joinTable = fromTable;
		}
		String xInfo = pieceXInfo(xFieldMap);
		String xInfoDetail = pieceXInfoDetail(aliaTabX, xFieldMap);
		String yInfo = pieceYInfo(yField, "", "", null);
		String yInfoDetail = pieceYInfoDetail(aliaTabY, yField, cfieldsArray, mulRelationData, mulRelaTable, map);
		// joinTable的字段关联 joinTable.column = x轴表的.column
		String equation = addPoind(aliaTabX, relaField) + " = " + addPoind(map.get(xOriginalTable), relaField);
		// 设置的多表关联中的关联关系 leftTable.column = rightTable.column and leftTable.column2 = rightTable.column2
		String relaTableColuumn = "";
		if (!mulRelationData.equals("{}")) {
			relaTableColuumn = relation;
		}
		String filterCondition = getWhereCondition(cfieldsArray, aliaTabY, map);
		String groupBy = xInfo;
		String orderBy = pieceOrderBy(yField);
		
		
		String sqlString = sqlTemplete(xInfo, yInfo, xInfoDetail, yInfoDetail,
				fromTable, aliaTabX, joinTable, aliaTabY, equation,
				filterCondition, orderBy, groupBy, relaTableColuumn, mulRelaTable, map);

		return sqlString;
	}

	/**
	 * 油量表sql各个片段的拼接
	 * 
	 * @param yField
	 * @param cfieldsArray
	 * @param fromTable
	 * @return
	 */
	public String getSqlDetail4Oil(YFieldsBean[] yField,
			CFieldsBean[] cfieldsArray, String fromTable, 
			Map<String, String> map, String mulRelaTable, String relation) {
		aliaTabY = map.get(fromTable);
		String yInfoDetail4Oil = pieceYInfo(yField, aliaTabY, fromTable, map);
		String filterCondition4Oil = getWhereCondition(cfieldsArray, aliaTabY, map);
		String sqlString4Oil = sqlTemplete4Oil(yInfoDetail4Oil, fromTable,
				filterCondition4Oil, relation, mulRelaTable, map);
		return sqlString4Oil;
	}

	/**
	 * 排序
	 * 
	 * @param yField
	 * @return
	 */
	private String pieceOrderBy(YFieldsBean[] yField) {
		String orderBy = "";
		for (YFieldsBean m : yField) {
			orderBy += m.getName() + " DESC ,";
		}
		return subString(orderBy);
	}

	/**
	 * 内查询y
	 * 
	 * @param aliaTabY
	 * @param yField
	 * @param cfieldsArray
	 * @return
	 */
	private String pieceYInfoDetail(String aliaTabY, YFieldsBean[] yField,
			CFieldsBean[] cfieldsArray, String mulRelationData, 
			String multiRealTable, Map<String, String> map) {
		// ty.BRND_CD as BRND_CD,ty.TM_INTRVL_CD
		String yInfoDetail = "";
		for (YFieldsBean m : yField) {
			if (!mulRelationData.equals("{}") && multiRealTable.equals(m.getTableName())) {
				yInfoDetail += addPoind(map.get(m.getTableName()), m.getName()) + " as "
						+ m.getName() + ",";
			} else {
				yInfoDetail += addPoind(map.get(m.getTableName()), m.getName()) + " as "
					+ m.getName() + ",";
			}
		}
		// 加形参
		for (int i = 0; i < cfieldsArray.length; i++) {
			CFieldsBean cBean = cfieldsArray[i];
			if (cBean.getParamType() != null) {
				if (cBean.getParamType().equals("formalPara")) {// 形参
					/*if (cBean.getTableName() == null) {
						yInfoDetail += addPoind(aliaTabY, cBean.getName()) + " as "
								+ cBean.getName() + ",";
					} else {*/
						yInfoDetail += addPoind(map.get(cBean.getTableName()), cBean.getName()) + " as "
							+ cBean.getName() + ",";
					//}
				}
			}
		}
		return subString(yInfoDetail);
	}

	/**
	 * 外查询y
	 * 
	 * @param yField
	 * @param aliasTable
	 * @return
	 */
	private String pieceYInfo(YFieldsBean[] yField, String aliasTable, String fromTable, Map<String, String> map) {
		String yInfo = "";
		// sum(BRND_CD) as BRND_CD,count(TM_INTRVL_CD) as TM_INTRVL_CD
		for (YFieldsBean m : yField) {
			if (m.getCustomStle() != null && !"".equals(m.getCustomStle())) {
				// 自定义统计
				yInfo += m.getCustomStle() + ",";
			} else {
				if ("".equals(aliasTable)) {
					yInfo += m.getFun() + " ( " + m.getName() + " ) as "
							+ m.getName() + ",";
				} else {
					if (!m.getTableName().equals(fromTable)) {
						yInfo += m.getFun() + " ( "
								+ addPoind(map.get(m.getTableName()), m.getName()) + " ) as "
								+ m.getName() + ",";
					} else {
						yInfo += m.getFun() + " ( "
							+ addPoind(aliasTable, m.getName()) + " ) as "
							+ m.getName() + ",";
					}
				}
			}
		}
		return subString(yInfo);
	}

	/**
	 * 外查询x
	 * 
	 * @param xFieldMap
	 * @return
	 */
	private String pieceXInfo(Map<String, String> xFieldMap) {
		String xInfo = "";
		// name, id
		for (Map.Entry<String, String> m : xFieldMap.entrySet()) {
			xInfo += m.getKey() + ",";
		}
		return subString(xInfo);
	}
	
	

	/**
	 * 条件查询
	 * 
	 * @param cFieldsBean
	 * @param aliaTabY
	 * @return
	 */
	private String getWhereCondition(CFieldsBean[] cFieldsBean, String aliaTabY, Map<String, String> map) {
		StringBuilder conditionStr = new StringBuilder();
		for (int i = 0; i < cFieldsBean.length; i++) {
			CFieldsBean cBean = cFieldsBean[i];
			if (cBean.getParamType() != null) {
				if (cBean.getTableName() == null) {
					/*conditionStr.append(" and " + aliaTabY);
					conditionStr.append("." + cBean.getName()
							+ " " + cBean.getOperator() + " '"
							+ cBean.getDefaultValue() + "' ");*/
				} else {
					conditionStr.append(" and " + map.get(cBean.getTableName()));
					conditionStr.append("." + cBean.getName()
							+ " " + cBean.getOperator() + " '"
							+ cBean.getDefaultValue() + "' ");
				}	
			}
		}
		return conditionStr.toString();
	}

	/**
	 * 内查询x
	 * 
	 * @param aliaTabX
	 * @param xFieldMap
	 * @return
	 */
	private String pieceXInfoDetail(String aliaTabX,
			Map<String, String> xFieldMap) {
		String xInfoDetail = "";
		// tx.VEST_LVL3_NAM as name, tx.VEST_LVL3_CD as id
		for (Map.Entry<String, String> m : xFieldMap.entrySet()) {
			xInfoDetail += addPoind(aliaTabX, m.getValue()) + " as "
					+ m.getKey() + ",";
		}
		return subString(xInfoDetail);
	}

	/**
	 * 油量表模板
	 * 
	 * @param yInfoDetail
	 * @param fromTable
	 * @param filterCondition
	 * @return
	 */
	private String sqlTemplete4Oil(String yInfoDetail, String fromTable,
			String filterCondition, String relation, String mulRelaTable, Map<String, String> map) {
		String sql = "";
		StringBuffer buff = new StringBuffer();
		buff.append("select ");
		buff.append(yInfoDetail);
		buff.append(" from ");
		buff.append(fromTable + " ");
		buff.append(aliaTabY);
		if (!mulRelaTable.equals("")) {
			buff.append(" join "+ mulRelaTable + " " + map.get(mulRelaTable) + " on " + relation);
		}
		buff.append(" where 1=1 ");
		buff.append(filterCondition + " ");
		sql = buff.toString();
		return sql;
	}

	/**
	 * 模板拼接sql
	 * 
	 * @param xInfo
	 * @param yInfo
	 * @param xInfoDetail
	 * @param yInfoDetail
	 * @param fromTable
	 * @param aliaTabX
	 * @param joinTable
	 * @param aliaTabY
	 * @param equation
	 * @param filterCondition
	 * @param orderBy
	 * @param groupBy
	 * @param relaTableColuumn 关联关系字段
	 * @param multiRealTable 设置的关联关系的表
	 * @return
	 */
	private String sqlTemplete(String xInfo, String yInfo, String xInfoDetail,
			String yInfoDetail, String fromTable, String aliaTabX,
			String joinTable, String aliaTabY, String equation,
			String filterCondition, String orderBy, String groupBy, 
			String relaTableColuumn, String multiRealTable, Map<String, String> map) {
		String subQuery = "";
		String joinDetail = "";
		String joinDetailOne="";
		String joinDetailTwo="";	
		// 关联的设置关联关系的那张表
		if (!fromTable.equals(joinTable) && !multiRealTable.equals("")) {
			joinDetailOne = " join " + multiRealTable + " " + map.get(multiRealTable) + " on "
					+ relaTableColuumn;
		}
		if (!fromTable.equals(joinTable)) {
			joinDetailTwo = " join " + joinTable + " " + aliaTabX + " on "
					+ equation;
		}
			joinDetail = joinDetailOne+joinDetailTwo;
		subQuery = "select " + xInfoDetail + " , " + yInfoDetail + " from "
				+ fromTable + " " + aliaTabY + joinDetail + " where 1=1 "
				+ filterCondition + " ";
		String sqlTemp = "select " + xInfo + " , " + yInfo + " from " + " ( "
				+ subQuery + " )	GROUP BY " + groupBy + " ORDER BY " + orderBy;
		System.out.println(sqlTemp);
		return sqlTemp;
	}

	/**
	 * 加表名
	 * 
	 * @param tableAlias
	 * @param field
	 * @return
	 */
	private String addPoind(String tableAlias, String field) {
		return tableAlias + "." + field;
	}

	/**
	 * 截取最后字符
	 * 
	 * @param str
	 * @param sign
	 * @return
	 */
	private String subString(String str) {
		return str.substring(0, str.length() - 1);

	}
	
	/**
	 * 正则判断 截取类似 count (T_VAL ) as T_VAL 字符串
	 * @param str
	 * @return
	 */
	public String splitString(String str){
		String regex="(\\s[a|A][s|S]\\s){1}";
		String apposite=null;
		if(str!=null && regex!=null){
			String[] splitStr=str.split(regex);
			apposite=splitStr[0];
		}
		return apposite;
		
	}
	
}
